# -*- coding:utf-8 -*-
from datetime import datetime

__author__ = 'george'

import os

import MySQLdb

import xlrd


def open_xls_file(filename):
    if os.path.exists(filename):
        return xlrd.open_workbook(filename, formatting_info=True)


def consist_insert_sql(columns, rowdata, tablename):
    """
    生成需要插入数据的SQL语句
    :arg
        columns:列名数组
        rowdata:行数据数组
        tablename:表名
    :returns
        返回需要的sql语句
    :raises
    """
    if len(columns) == len(rowdata):
        sql_commnad = "insert into %s (%s) values (%s)" % (tablename,
                                                           reduce(lambda c1, c2: c1 + ' , ' + c2, columns),
                                                           reduce(lambda a, b: a + "," + b,
                                                                  map(lambda a: "'" + a + "'", rowdata)))
        print sql_commnad
        return sql_commnad


def get_db_connection(host='192.168.2.7', port=3306, user='dev', passwd='1qaz2wsx', db='jeesite', use_unicode=True):
    """
    获取数据库的连接对象
    :returns
        返回 cursor 对象
    """
    connection = MySQLdb.connect(host, user, passwd, db, port, use_unicode=True, charset="utf8")
    cursor = connection.cursor()
    # connection.set_character_set('utf-8')
    cursor.execute('SET NAMES utf8')
    cursor.execute('SET CHARACTER SET utf8')
    cursor.execute('SET character_set_connection=utf8')
    return cursor


def import_data(filename):
    """
    从excel中导入数据到mysql中的数据表
    """
    workbook = open_xls_file(filename)

    sheets = (workbook.sheet_by_name(name) for name in workbook.sheet_names())
    for sheet in sheets:
        nclos = sheet.ncols
        nrows = sheet.nrows
        for rindex, row in enumerate(range(0, nrows)):
            if (rindex == 0):
                columns = [sheet.cell(rindex, col).value for col in range(0, nclos)]
            else:
                rowdata=[]
                for cindex in range(0, nclos):
                    cell_type = sheet.cell_type(rindex,cindex)
                    cell_value=sheet.cell_value(rindex,cindex)

                    if cell_type == xlrd.XL_CELL_BLANK:
                        value = None
                    elif cell_type == xlrd.XL_CELL_BOOLEAN:
                        value = bool(cell_value)
                    elif cell_type == xlrd.XL_CELL_DATE:
                        value = datetime(*xlrd.xldate_as_tuple(cell_value, workbook.datemode)).strftime('%Y-%m-%d')

                    elif cell_type == xlrd.XL_CELL_NUMBER:
                        value = int(cell_value)
                    else:
                        value = cell_value
                    rowdata.append(value)

                sql_command = consist_insert_sql(columns, map(lambda a:'%s'%a,rowdata), sheet.name)
                cursor = get_db_connection()
                cursor.execute(sql_command)


if __name__ == "__main__":
    file1 = "/home/george/git/jeesite/db/sys/jeesite_data.xls"
    file2 = "/home/george/git/jeesite/db/cms/jeesite_data.xls"
    import_data(file1)
    import_data(file2)
    pass
